package com.dream.wukong.common.core.jdbc;

import com.alibaba.fastjson.JSON;
import com.dream.wukong.common.core.cache.ehcache.EhcacheCache;
import com.dream.wukong.common.core.interfaces.cache.ICacheService;
import com.dream.wukong.common.core.interfaces.dao.PageResult;
import com.dream.wukong.common.core.interfaces.jdbc.INativeQuery;
import com.dream.wukong.common.core.jdbc.dialect.Dialect;
import com.dream.wukong.common.core.jdbc.dialect.impl.MySQLDialect;
import com.dream.wukong.common.core.jdbc.dialect.impl.OracleDialect;
import com.dream.wukong.common.core.jdbc.sqlconfig.SqlMapper;
import com.dream.wukong.common.core.jdbc.sqltemplate.SqlTemplate;
import com.dream.wukong.common.core.jdbc.sqltemplate.impl.VelocitySqlTemplate;
import com.dream.wukong.common.core.util.EncryptUtil;
import com.dream.wukong.common.core.util.MapUtils;
import com.dream.wukong.common.core.util.PropertiesUtils;
import com.dream.wukong.common.core.util.StringUtil;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.*;
import org.springframework.jdbc.support.JdbcAccessor;
import org.springframework.util.StringUtils;

import java.util.*;

/**
 1.实现本地化SQL查询 2.SQL可配置可热部署
 * @Author: wuyichao
 * @Description:
 * @Date: Created in 17:25 2018/9/17.
 * @Modified:
 */
public class JdbcNativeQuery extends JdbcAccessor implements INativeQuery {
    protected Log log = LogFactory.getLog(getClass());

    protected NamedParameterJdbcOperations jdbc;

    private static int DEFAULT_PAGE_SIZE = 50;

    protected ICacheService dataCache = new EhcacheCache(getClass().getName());

    protected SqlTemplate sqlTemplate = new VelocitySqlTemplate();

    private static boolean dbType = (StringUtils.isEmpty(PropertiesUtils
            .getProperty("system.database.dbType"))
            || PropertiesUtils.getProperty("system.database.dbType")
            .equalsIgnoreCase("oracle"))?true:false;

    protected Dialect dialect = dbType?new OracleDialect():new MySQLDialect();

    /**
     *
     * @param sqlKey
     * @param map
     * @return
     */
    public List<Map<String, Object>> selectAllList(String sqlKey,
                                                   Map<String, Object> map) {
        String cacheKey = getCacheKey(sqlKey, map);
        SqlMapper sqlMapper = sqlTemplate.getSqlMapper(sqlKey);
        String sql = sqlTemplate.getSql(sqlKey, map);
        if (sqlMapper == null) {
            throw new RuntimeException("load sqlconfig error :" + sqlKey);
        }
        if (sqlMapper.isFlushCache()) {
            dataCache.delete(cacheKey);
        }
        if (sqlMapper.isUseCache()) {
            if (dataCache.get(cacheKey) != null) {
                log.debug("load from cacheKey : " + cacheKey);
                Object cacheVal = dataCache.get(cacheKey);
                if(cacheVal instanceof String ) {
                    log.debug("load from dataCache : " + dataCache );
                    List<Map> blist = JSON.parseArray((String)cacheVal, Map.class) ;
                    //规避泛型检查
                    List<Map<String, Object>> cList = new ArrayList<Map<String, Object>>();
                    for (Map map2 : blist) {
                        cList.add( new MapUtils(map2).toSimpleMap());
                    }
                    return cList ;
                }
                return (List<Map<String, Object>>) dataCache.get(cacheKey);
            } else {
                log.debug("reload   cacheKey : " + cacheKey);
                List<Map<String, Object>> rs = this.queryForList(sql, map);
                dataCache.put(cacheKey, rs);
                return rs;
            }
        } else {
            List<Map<String, Object>> rs = this.queryForList(sql, map);
            return rs;
        }
    }



    /**
     * @param sqlKey
     * @param map
     * @param offset
     * @param limit
     * @return offset+1~offset+limit
     */
    public List<Map<String, Object>> selectPageList(String sqlKey,
                                                    Map<String, Object> map, int offset, int limit) {
        log.debug("offset " + offset + " limit " + limit);
        long startTime = System.currentTimeMillis();
        String sql = sqlTemplate.getSql(sqlKey, map);
        if (dialect != null) {
            sql = dialect.getPageSql(sql, offset, limit, map);
            long endTime = System.currentTimeMillis();
            log.debug("getSql costTime ms :" + (endTime - startTime));
            log.debug(sql);
            log.debug(map);
            return this.queryForList(sql, map);
        } else {
            return selectLogicPageList(sql, map, offset, limit);
        }

    }



    /**
     * @param sql
     * @param map
     * @param offset
     * @param limit
     * @return offset+1~offset+limit
     */
    public List<Map<String, Object>> selectPageListBySql(String sql,
                                                         Map<String, Object> map, int offset, int limit) {
        log.debug("offset " + offset + " limit " + limit);
        long startTime = System.currentTimeMillis();
        if (dialect != null) {
            sql = dialect.getPageSql(sql, offset, limit, map);
            long endTime = System.currentTimeMillis();
            log.debug("getSql costTime ms :" + (endTime - startTime));
            log.debug(sql);
            log.debug(map);
            return this.queryForList(sql, map);
        } else {
            return selectLogicPageList(sql, map, offset, limit);
        }

    }


    public <T> List<T> selectPageListBySql(String sql,
                                           Map<String, Object> map, int offset, int limit,Class<T> cls) {
        log.debug("offset " + offset + " limit " + limit);
        long startTime = System.currentTimeMillis();
        if (dialect != null) {
            sql = dialect.getPageSql(sql, offset, limit, map);
            long endTime = System.currentTimeMillis();
            log.debug("getSql costTime ms :" + (endTime - startTime));
            log.debug(sql);
            log.debug(map);
            return this.queryForList(sql, map ,cls);
        } else{
            return null;
        }
    }

    /**
     * 该方法只适用于单表分页查询
     * @param sqlKey
     * @param map
     * @param pageResult
     * @param cls
     * @return
     */
    public <T> PageResult<T> selectPageResult(String sqlKey,
                                              Map<String, Object> map, PageResult<T> pageResult, Class<T> cls) {
        long startTime = System.currentTimeMillis();
        String sql = sqlTemplate.getSql(sqlKey, map);
        int limit = pageResult.getPageSize();
        int offset = (int) ((pageResult.getPageNo()-1)*pageResult.getPageSize());
        if (dialect != null) {
            sql = dialect.getPageSql(sql, offset, limit, map);
            long endTime = System.currentTimeMillis();
            log.debug("getSql costTime ms :" + (endTime - startTime));
            log.debug(sql);
            log.debug(map);
            pageResult.setRows(this.queryForList(sql, map,cls));
        } else {
            pageResult.setRows(selectLogicPageResult(sql, map, offset, limit,cls));
        }
        return pageResult;
    }

    public <T> PageResult<T> selectPageResultBySql(String sql,
                                                   Map<String, Object> map, PageResult<T> pageResult,Class<T> cls) {
        long startTime = System.currentTimeMillis();
        if (dialect != null) {
            int limit = pageResult.getPageSize();
            int offset = (int) ((pageResult.getPageNo()-1)*pageResult.getPageSize());
            sql = dialect.getPageSql(sql, offset, limit, map);
            long endTime = System.currentTimeMillis();
            log.debug("getSql costTime ms :" + (endTime - startTime));
            log.debug(sql);
            log.debug(map);
            pageResult.setRows(this.queryForList(sql, map ,cls));
        } else{
            return null;
        }
        return pageResult;
    }

    /**
     * @param sql
     * @param map
     * @param offset
     * @param limit
     * @return offset+1~offset+limit
     */
    public List<Map<String, Object>> selectLogicPageList(String sql,
                                                         Map<String, Object> map, int offset, int limit) {
        log.debug("offset " + offset + " limit " + limit);
        log.debug(sql);
        List<Map<String, Object>> rsList = new ArrayList<Map<String, Object>>(
                limit);
        List<Map<String, Object>> ls = this.queryForList(sql, map);
        // offset+1~offset+limit
        for (int rownum = 0; rownum < ls.size() && rownum <= offset + limit; rownum++) {
            log.debug(ls.get(rownum));
            if (rownum >= offset + 1 && rownum <= offset + limit) {
                log.debug("add ...." + ls.get(rownum));
                rsList.add(ls.get(rownum));
            }
        }
        return rsList;
    }

    /**
     * 该方法只适用于单表的查询
     * @param sql
     * @param map
     * @param offset
     * @param limit
     * @param cls
     * @return
     */
    public <T> List<T> selectLogicPageResult(String sql,
                                             Map<String, Object> map, int offset, int limit,Class<T> cls) {
        log.debug("offset " + offset + " limit " + limit);
        log.debug(sql);
        List<T> rsList = new ArrayList<T>(limit);
        List<T> ls = this.queryForList(sql, map,cls);
        // offset+1~offset+limit
        for (int rownum = 0; rownum < ls.size() && rownum <= offset + limit; rownum++) {
            log.debug(ls.get(rownum));
            if (rownum >= offset + 1 && rownum <= offset + limit) {
                log.debug("add ...." + ls.get(rownum));
                rsList.add(ls.get(rownum));
            }
        }
        return rsList;
    }

    /**
     *
     * @param sqlkey
     * @param map
     * @return
     */
    public List<Map<String, Object>> selectPageList(String sqlkey,
                                                    Map<String, Object> map) {
        MapUtils<String, Object> rm = new MapUtils<String, Object>(map);
        int offset = rm.getInt(Dialect.OFFSET);
        int limit = rm.hasKey(Dialect.LIMIT) ? rm.getInt(Dialect.LIMIT)
                : DEFAULT_PAGE_SIZE;
        return this.selectPageList(sqlkey, map, offset, limit);
    }

    /**
     *
     * @param map
     * @return
     * @throws Exception
     */
    public Long selectCount(String sqlKey, Map<String, Object> map) {
        String sql = dialect.getCountString(sqlTemplate.getSql(sqlKey, map));
        return this.queryForObject(sql, map, Long.class);
    }

    /**
     *
     * @param map
     * @return
     * @throws Exception
     */
    public Long selectCountBySql(String sql, Map<String, Object> map) {
        sql = dialect.getCountString(sql);
        return this.queryForObject(sql, map, Long.class);
    }

    /**
     * 还原SQL转化过程
     * @param sql
     * @param map
     * @return
     */
    public String getExecuteSql(String sql, Map<String, Object> map) {
        ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql);
        MapSqlParameterSource paramSource = new MapSqlParameterSource(map);
        String sqlToUse = NamedParameterUtils.substituteNamedParameters(
                parsedSql, paramSource);
        Object[] params = NamedParameterUtils.buildValueArray(parsedSql,
                paramSource, null);
        List<Object> paramList = Arrays.asList(params);
        String executeSql = getSql(sqlToUse, paramList);
        log.debug("sql :" + sql);
        log.debug("parsedSql :" + parsedSql);
        log.debug("sqlToUse :" + sqlToUse);
        log.debug("params :" + paramList);
        log.debug("executeSql :" + executeSql);
        return executeSql;
    }

    /**
     * sql ( select * from table where user_id = :userId ) Convert to standard
     * jdbc sql ( select * from table where user_id = ?) ;
     *
     * @param sql
     * @param map
     * @param list
     * @return
     */
    @SuppressWarnings("all")
    private String getSql(String sql, List<Object> lstParam) {
        for (int i = 0; i < lstParam.size(); i++) {
            Object value = lstParam.get(i);
            if (value instanceof Integer || value instanceof Double
                    || value instanceof Float || value instanceof Long) {
                sql = StringUtil.replaceOnce(sql, "?", value.toString());
            } else if (value instanceof String) {
                sql = StringUtil.replaceOnce(sql, "?", "'" + value.toString()
                        + "'");
            } else {
                sql = StringUtil.replaceOnce(sql, "?",
                        "'" + String.valueOf(value) + "'");
            }
        }
        return sql;
    }

    private String getCacheKey(String sqlKey, Map<String, Object> qryMap) {
        StringBuffer cacheKey = new StringBuffer(CACHE_KEY_PREFIX+sqlKey);
        SortedMap<String, Object> sortParams = new TreeMap<String, Object>(
                qryMap);
        List<String> paramList = new ArrayList<String>(4);
        for (String paramKey : sortParams.keySet()) {
            String value = StringUtil.asString(sortParams.get(paramKey));
            if ("sqlKey".equals(paramKey)||!StringUtils.hasLength(value)) {
                continue;
            }
            paramList.add(paramKey + "=" + value);
        }
        //如果参数列表不为空，MD5编码后以_分割；
        if(!paramList.isEmpty()) {
            String str1 = StringUtils.collectionToDelimitedString(paramList, "&");
            String paramStr = EncryptUtil.encodeStr(str1).toUpperCase();
            cacheKey.append("_").append(paramStr);
        }
        return cacheKey.toString();
    }


    @Override
    public List<Map<String, Object>> queryForList(String sql, Map<String, Object> map) {
        return jdbc.queryForList(sql, map);
    }

    public <T> List<T> queryForList(String sql, Map<String, Object> map,Class<T> cls) {
        return jdbc.query(sql,map,new BeanPropertyRowMapper(cls));
    }

    @Override
    public Map<String, Object> queryForMap(String sql, Map<String, Object> paramMap) {
        return jdbc.queryForMap(sql, paramMap);
    }

    @Override
    public <T> T queryForObject(String sql, Map<String, Object> paramMap,
                                Class<T> requiredType) {
        return jdbc.queryForObject(sql, paramMap, requiredType);
    }

    public void setDataCache(ICacheService dataCache) {
        log.debug("setDataCache implments =" + dataCache);
        this.dataCache = dataCache;
    }

    public void setSqlTemplate(SqlTemplate sqlTemplate) {
        this.sqlTemplate = sqlTemplate;
    }

    public void setDialect(Dialect dialect) {
        this.dialect = dialect;
    }

    public JdbcNativeQuery() {
        super();
    }

    public JdbcNativeQuery(
            NamedParameterJdbcOperations namedParameterJdbcTemplate) {
        super();
        jdbc = namedParameterJdbcTemplate;
    }

    public void setJdbc(NamedParameterJdbcOperations jdbc) {
        this.jdbc = jdbc;
    }

    public void afterPropertiesSet() {
        if (jdbc != null)
            return;
        super.afterPropertiesSet();
        jdbc = new NamedParameterJdbcTemplate(new JdbcTemplate(
                this.getDataSource()));
    }

}